---
title: "cp_external_client_lookup stored procedure documentation"
summary: "Analysis of the cp_external_client_lookup SQL Server (BDS) stored procedure including business rules and operations."
status: draft
owner: "SAIF Corporation"
last_reviewed: 2026-08-31
source_schema: "eisD0P11.dbo"
source_name: "cp_external_client_lookup"
source_type: "PROCEDURE"
retrieval_status: "found"
retrieval_command: "legends sqlserver query --name cp_external_client_lookup"
retrieval_date: "2026-08-31"
---

# cp_external_client_lookup

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `eisD0P11.dbo` |
| **Type** | SQL Server stored procedure (BDS) |
| **Database** | SQL Server (`eisD0P11`, referred to internally as "BDS") |
| **Status** | ✅ `found` — retrieved via Legends CLI |
| **Query executed** | `legends sqlserver query --name cp_external_client_lookup` |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsTeamAssignment` |

## Purpose

`cp_external_client_lookup` returns the most recent active, non-test registrant matching an id number
and last name, used by `clsTeamAssignment` (`clsTeamAssignment.vb:381-390`). This procedure is distinct
from `sp73341_external_client_lookup` (an Oracle procedure documented separately) despite the similar
name; both are invoked from the same caller class.

## Procedures / Methods

### cp_external_client_lookup

| Field | Value |
| --- | --- |
| **Returns** | `TOP 1 *` from `EIS_PROD.dbo.Net_Registrant` |
| **Called by** | `clsTeamAssignment` (`clsTeamAssignment.vb:384`, parameters `386-387`) |

**Description:** Comment header attributes the procedure to `JERTOD 01/08/2007`. Matches on trimmed id
number and last name, restricted to active non-test registrants, and returns only the single most
recently updated match (see rule PR-BVP-063 in [Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `@idNum` | IN | `char(100)` | — | Registrant id number |
| `@clientLastName` | IN | `char(255)` | — | Client last name |

## Business Rules

Rules extracted from this procedure's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-BVP-063 | Only one active, non-test registrant is returned — the most recently updated | `rtrim(ID_NUM) = rtrim(@idNum)` AND `rtrim(USER_LAST_NAM) = rtrim(@clientLastName)` AND `ACTIVE_FL = 'Y'` AND `TEST_USER_FL = 'N'` | `select top 1 ... order by UPDATE_DATE desc` from `EIS_PROD.dbo.Net_Registrant` |

> This rule is documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| Look up external client (BDS) | Returns the most recent active, non-test registrant matching id and last name | `clsTeamAssignment` (`clsTeamAssignment.vb:381-390`) |

> This operation is also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>Transact-SQL stored procedure</summary>

```sql
-- JERTOD 01/08/2007
CREATE PROCEDURE dbo.cp_external_client_lookup
    @idNum          char(100),
    @clientLastName char(255)
AS
SELECT TOP 1 *
  FROM EIS_PROD.dbo.Net_Registrant
 WHERE rtrim(ID_NUM)        = rtrim(@idNum)
   AND rtrim(USER_LAST_NAM) = rtrim(@clientLastName)
   AND ACTIVE_FL   = 'Y'
   AND TEST_USER_FL = 'N'
 ORDER BY UPDATE_DATE DESC
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- SQL Server BDS source: `eisD0P11.dbo.cp_external_client_lookup`
- Legends CLI query executed 2026-08-31: `legends sqlserver query --name cp_external_client_lookup`
- Calling code: `SAIF.Business.View.Policy/clsTeamAssignment.vb:381-390`